
ALTER PROCEDURE SP_CREATE_UPDATE_SPLIT_LOT 
(	
	@PROCESSSTEP_ID		INT,
	@LOCATION_ID		INT,	
	@LOT_ID			INT,
	@PS_TYPE		VARCHAR(50),
	@SPLITONE_LOT_NO 	VARCHAR(50), 
	@SPLITTWO_LOT_NO 	VARCHAR(50),
	@SPLITONE_QTYIN		INT,
    @SPLITTWO_QTYIN		INT,
	@SPLITONE_WAFER_ID 	VARCHAR(100),
	@SPLITTWO_WAFER_ID 	VARCHAR(100),
	@LOT_COMMENTS 		VARCHAR(500),
	@USER_NAME 		VARCHAR(50) 
) 
as
begin		
	DECLARE	@PO_NO				VARCHAR(20)
	DECLARE	@SHOP_ORDER_NO		VARCHAR(20)
	DECLARE	@LOT_CATEGORY 		VARCHAR(10)
	DECLARE	@FAB_LOT_NUMBER 	VARCHAR(50)
	DECLARE	@LOT_DATE_CODE 		VARCHAR(50)
	DECLARE	@LOT_STATUS 		VARCHAR(50)
	DECLARE	@DATE_IN 			DATETIME
	
	DECLARE	@CHILD_LOT_ID_ONE	INT
	DECLARE	@CHILD_LOT_ID_TWO 	INT
	
	SET @DATE_IN = CURRENT_TIMESTAMP

	
	--GETTING THE INITIAL LOT DETAILS
	SELECT @PO_NO = PO_NUMBER, @SHOP_ORDER_NO = SHOP_ORDER_NUMBER, @LOT_CATEGORY = LOT_CATEGORY, 
	       @FAB_LOT_NUMBER = FAB_LOT_NUMBER, @LOT_DATE_CODE = LOT_DATE_CODE,@LOT_STATUS = LOT_CURRENT_STATUS 
	       FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	
	--UPDATE SPLIT STATUS FOR INITIAL LOT
	UPDATE LOT_TABLE SET LOT_SPLIT = 0, LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
	
	IF @PS_TYPE = 'FAB' OR @PS_TYPE = 'FAB OPTION'
	BEGIN
		--INSERTING THE SPLIT LOTS INTO LOT_TABLES (FAB_LOT AND LOT NO ARE SAME)
		EXEC sp_create_update_data_entry_fab_lot @PROCESSSTEP_ID, @LOCATION_ID, @SPLITONE_LOT_NO, @SPLITONE_QTYIN, null, @PO_NO, @SHOP_ORDER_NO, @DATE_IN,null,null/*vendor Name*/, @SPLITONE_WAFER_ID,@LOT_CATEGORY, @SPLITONE_LOT_NO,@LOT_DATE_CODE,@LOT_STATUS,@LOT_COMMENTS
		EXEC sp_create_update_data_entry_fab_lot @PROCESSSTEP_ID, @LOCATION_ID, @SPLITTWO_LOT_NO, @SPLITTWO_QTYIN, null, @PO_NO, @SHOP_ORDER_NO, @DATE_IN,null,null/*vendor Name*/, @SPLITTWO_WAFER_ID,@LOT_CATEGORY, @SPLITTWO_LOT_NO,@LOT_DATE_CODE,@LOT_STATUS,@LOT_COMMENTS
	END
	ELSE IF @PS_TYPE = 'WAFER STORE' OR @PS_TYPE = 'WAFER BANK'
	BEGIN
		--INSERTING THE SPLIT LOTS INTO LOT_TABLES (FAB_LOT AND LOT NO ARE SAME)
		EXEC sp_create_update_data_entry_inventory_lot @PROCESSSTEP_ID, @LOCATION_ID, @SPLITONE_LOT_NO, @SPLITONE_LOT_NO, @SPLITONE_QTYIN, null, null/*DIE QTY*/, null, @DATE_IN,null,null/*vendor Name*/,@SPLITONE_WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_STATUS,@PS_TYPE, @LOT_COMMENTS, null,@USER_NAME, -1, -1
		EXEC sp_create_update_data_entry_inventory_lot @PROCESSSTEP_ID, @LOCATION_ID, @SPLITTWO_LOT_NO, @SPLITTWO_LOT_NO, @SPLITTWO_QTYIN, null, null, null, @DATE_IN,null,null/*vendor Name*/,@SPLITTWO_WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_STATUS,@PS_TYPE, @LOT_COMMENTS, null,@USER_NAME, -1, -1
	END
	ELSE IF @PS_TYPE = 'SORT'
	BEGIN
		--NEED TO IMPLEMENT SORT
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
	END
	ELSE IF @PS_TYPE = 'DIE BANK'
	BEGIN
		--INSERTING THE SPLIT LOTS INTO LOT_TABLES (FAB_LOT AND LOT NO ARE SAME)
		EXEC sp_create_update_data_entry_inventory_lot @PROCESSSTEP_ID, @LOCATION_ID, @SPLITONE_LOT_NO, @SPLITONE_LOT_NO, null/*WAFER QTY*/, null, @SPLITONE_QTYIN, null, @DATE_IN,null,null/*vendor Name*/,@SPLITONE_WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_STATUS,@PS_TYPE, @LOT_COMMENTS, null,@USER_NAME, -1, -1
		EXEC sp_create_update_data_entry_inventory_lot @PROCESSSTEP_ID, @LOCATION_ID, @SPLITTWO_LOT_NO, @SPLITTWO_LOT_NO, null/*WAFER QTY*/, null, @SPLITTWO_QTYIN, null, @DATE_IN,null,null/*vendor Name*/,@SPLITTWO_WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_STATUS,@PS_TYPE, @LOT_COMMENTS, null,@USER_NAME, -1, -1
	END
	ELSE IF @PS_TYPE = 'ASSEMBLY'
	BEGIN
		--INSERTING THE SPLIT LOTS INTO LOT_TABLES (FAB_LOT AND LOT NO ARE SAME)
		EXEC sp_create_update_data_entry_assembly_lot @PROCESSSTEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @SPLITONE_QTYIN, null, @PO_NO, @SHOP_ORDER_NO, @DATE_IN,null,null/*vendor Name*/, @SPLITONE_WAFER_ID,@LOT_CATEGORY, @SPLITONE_LOT_NO,@LOT_DATE_CODE,@LOT_STATUS,@LOT_COMMENTS, @USER_NAME, -1, -1
		EXEC sp_create_update_data_entry_assembly_lot @PROCESSSTEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @SPLITTWO_QTYIN, null, @PO_NO, @SHOP_ORDER_NO, @DATE_IN,null,null/*vendor Name*/, @SPLITTWO_WAFER_ID,@LOT_CATEGORY, @SPLITTWO_LOT_NO,@LOT_DATE_CODE,@LOT_STATUS,@LOT_COMMENTS, @USER_NAME, -1, -1
	END
	ELSE IF @PS_TYPE IN ('TEST', 'QA','FT','FPO', 'QC', 'BAKING', 'BALL SCAN', 'RE TEST')
	BEGIN	
		--INSERTING THE SPLIT LOTS INTO LOT_TABLES (FAB_LOT AND LOT NO ARE SAME)
		EXEC sp_create_update_data_entry_test_lot @PROCESSSTEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @SPLITONE_QTYIN, null, @PO_NO, @SHOP_ORDER_NO, @DATE_IN,null,null/*vendor Name*/, @SPLITONE_WAFER_ID,@LOT_CATEGORY, @SPLITONE_LOT_NO,@LOT_DATE_CODE,@LOT_STATUS,@LOT_COMMENTS, @USER_NAME, -1, -1
		EXEC sp_create_update_data_entry_test_lot @PROCESSSTEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @SPLITTWO_QTYIN, null, @PO_NO, @SHOP_ORDER_NO, @DATE_IN,null,null/*vendor Name*/, @SPLITTWO_WAFER_ID,@LOT_CATEGORY, @SPLITTWO_LOT_NO,@LOT_DATE_CODE,@LOT_STATUS,@LOT_COMMENTS, @USER_NAME, -1, -1
	END
	ELSE IF @PS_TYPE IN ('CLASS STORE', 'Bake & Dry', 'MARK & PACK', 'TAPE & REEL', 'Pre Test','EOL','FINISHED GOODS', 'FG')
	BEGIN
		--INSERTING THE SPLIT LOTS INTO LOT_TABLES (FAB_LOT AND LOT NO ARE SAME)
		EXEC sp_create_update_data_entry_inventory_lot @PROCESSSTEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @SPLITONE_LOT_NO, null/*WAFER QTY*/, null, @SPLITONE_QTYIN, null, @DATE_IN,null,null/*vendor Name*/,@SPLITONE_WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_STATUS,@PS_TYPE, @LOT_COMMENTS, null,@USER_NAME, -1, -1 
		EXEC sp_create_update_data_entry_inventory_lot @PROCESSSTEP_ID, @LOCATION_ID, @FAB_LOT_NUMBER, @SPLITTWO_LOT_NO, null/*WAFER QTY*/, null, @SPLITTWO_QTYIN, null, @DATE_IN,null,null/*vendor Name*/,@SPLITTWO_WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_STATUS,@PS_TYPE, @LOT_COMMENTS, null,@USER_NAME, -1, -1
	END
	
	SELECT @CHILD_LOT_ID_ONE = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @SPLITONE_LOT_NO
	SELECT @CHILD_LOT_ID_TWO = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @SPLITTWO_LOT_NO

	INSERT INTO Lot_Split_Table (Parent_Lot_Id, Child_Lot_Id1,Child_Lot_Id2, User_Name, TIMESTAMP )
			VALUES (@LOT_ID, @CHILD_LOT_ID_ONE, @CHILD_LOT_ID_TWO, @USER_NAME, @DATE_IN )
	
end
